#include "DatabaseUtil.h"

DatabaseUtil* DatabaseUtil::singleObjPtr = nullptr;

bool DatabaseUtil::AddItem(const std::string& tableName, const Json::Value& data, std::string& message, int& code)
{
    std::ostringstream sql;
    std::ostringstream keys;
    std::ostringstream values;
    Json::Value::Members members = data.getMemberNames();
    char* errorMsg;

    for (const auto& name : members) {
        keys << name << ',';
        if(!DataTypeConvert(data, name, values, false))
        {
            message = "数据格式错误：" +  name + " " + data[name].asString();
            code = 400;
            return false;
        }
        values << ','; 
    }

    keys << "GUID";
    values << '\'' << Tools::GenRandom(16) << '\'';
    std::string keyString = keys.str();
    std::string valueString = values.str();

    sql << "INSERT INTO " << tableName << " (" << keyString << ") VALUES (" << valueString << ')';
    if(!sqlite3_exec(db, sql.str().c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK)
    {
        std::cout << "插入数据失败，相关信息：" << errorMsg << std::endl << "相关sql语句：" << sql.str() << std::endl;
        code = 500;
        message = errorMsg;
        return false;
    }

    code = 200;
    message = "数据添加成功";
    return true;
}

bool DatabaseUtil::EditItem(const std::string& tableName, const Json::Value& data, std::string& message, int& code) 
{
    std::ostringstream sql;
    std::ostringstream operation;
    char* errorMsg;
    Json::Value::Members members = data.getMemberNames();
    for (const auto& name : members) {
        if(name == "GUID") continue;
        operation << name << '=';
        if (!DataTypeConvert(data, name, operation, false))
        {
            message = "数据格式错误：" +  name + " " + data[name].asString();
            code = 400;
            return false;
        }
        operation << ',';
    } 
    std::string oString = operation.str();

    sql << "UPDATE " << tableName << " SET " << oString.substr(0, oString.length()-1) << " WHERE GUID=\'" << data["GUID"].asString() << '\'';
    if(!sqlite3_exec(db, sql.str().c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK)
    {
        std::cout << "更改数据失败，相关信息：" << errorMsg << std::endl << "相关sql语句：" << sql.str() << std::endl;
        code = 500;
        message = errorMsg;
        return false;
    }

    code = 200;
    message = "数据修改成功";
    return true;
}

bool DatabaseUtil::DeleteItem(const std::string& tableName, const std::string& guid, std::string& message, int& code)
{
    std::ostringstream sql;
    char* errorMsg;

    sql << "DELETE FROM " << tableName;
    if(guid != "all") sql << " WHERE GUID=\'" << guid << '\'';
    
    if(!sqlite3_exec(db, sql.str().c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK)
    {
        std::cout << "删除数据失败，相关信息：" << errorMsg << std::endl << "相关sql语句：" << sql.str() << std::endl;
        code = 500;
        message = errorMsg;
        return false;
    }

    code = 200;
    message = "数据删除成功";
    return true;
}

bool DatabaseUtil::QueryItems(const std::string& tableName, const Json::Value& condition, Json::Value& datas, Json::Value& pageInfo, bool isFuzzy, std::string& message, int& code)
{
    //分页查询参数
    int page = 1;
    int pageCount = 10000;

    std::ostringstream sql;
    char* errorMsg;
    sql << "SELECT * FROM " << tableName << " WHERE ";

    bool haveCondition = false;
    Json::Value::Members members = condition.getMemberNames();
    for (const auto& name : members) {
        //提取分页查询参数
        if(name == "Page" || name == "PageCount")
        {
            try
            {
                page = condition["Page"].asInt();
                pageCount = condition["PageCount"].asInt();
            }
            catch(const std::exception& e)
            {
                message = "分页查询数据格式错误：" +  name + " " + condition[name].asString();
                code = 400;
                return false;
            }

            continue;
        }

        //提取时间信息
        if(name == "TimeStampStart" || name == "TimeStampEnd")
        {
            try
            {
                long timeStamp = condition[name].asInt64();
                if(name == "TimeStampStart" && tableName == "AlarmInfo") sql << "AlarmTimeStamp > " << timeStamp << " AND ";
                if(name == "TimeStampEnd" && tableName == "AlarmInfo") sql << "AlarmTimeStamp < " << timeStamp << " AND ";
            }
            catch(const std::exception& e)
            {
                message = "时间查询数据格式错误：" +  name + " " + condition[name].asString();
                code = 400;
                return false;
            }
            haveCondition = true;
            continue;
        }

        //提取时间信息
        if(name == "AlarmTime")
        {
            try
            {
                struct tm timeStructStart = {0};
                strptime(condition[name][0].asString().c_str(), "%Y-%m-%d %H:%M:%S", &timeStructStart);
                long timeStampStart = mktime(&timeStructStart);

                struct tm timeStructEnd = {0};
                strptime(condition[name][1].asString().c_str(), "%Y-%m-%d %H:%M:%S", &timeStructEnd);
                long timeStampEnd = mktime(&timeStructEnd);

                sql << "AlarmTimeStamp > " << timeStampStart << " AND ";
                sql << "AlarmTimeStamp < " << timeStampEnd << " AND ";
            }
            catch(const std::exception& e)
            {
                message = "时间查询数据格式错误：" +  name + " " + condition[name].asString();
                code = 400;
                return false;
            }
            haveCondition = true;
            continue;
        }

        //拼接查询语句
        sql << name << " LIKE ";
        if (!DataTypeConvert(condition, name, sql, isFuzzy))
        {
            message = "数据格式错误：" +  name + " " + condition[name].asString();
            code = 400;
            return false;
        }
        sql << " AND ";
        haveCondition = true;
    }
    
    std::string oString = sql.str();
    if(haveCondition) oString = oString.substr(0, oString.length()-5);
    else oString = oString.substr(0, oString.length()-7);

    //报警信息倒序排列
    if(tableName == "AlarmInfo")
    {
        oString += " ORDER BY AlarmTimeStamp DESC";
    }

    char** result;
    int row, column;
    if(!sqlite3_get_table(db, oString.c_str(), &result, &row, &column, &errorMsg) == SQLITE_OK)
    {
        std::cout << "查找数据失败，相关信息：" << errorMsg << std::endl << "相关sql语句：" << sql.str() << std::endl;
        code = 500;
        message = errorMsg;
        return false;
    }

    code = 200;
    message = "数据查询成功";

    if(row == 0)
    {
        std::cout << "未查找到相关数据，相关sql语句：" << std::endl << sql.str() << std::endl;
        message = "未查询到相关数据";
        return true;
    }

    pageInfo["TotalRow"] = row;
    int startRow = 1;
    if(pageCount > 0)
    {
        row = (page * pageCount > row) ? row : (page * pageCount);
        startRow = (page - 1) * pageCount + 1;
        pageInfo["TotalPage"] = (int)ceil((row/((double)pageCount)));
        pageInfo["CurPage"] = page;
        pageInfo["RowPerPage"] = pageCount;
    }

    int order = 0;
    for (int i = startRow; i <= row; i++)
    {
        Json::Value data;
        for (int j = 0; j < column; j++)
        {
            std::string name = result[j];
            if(!result[column*i + j]) continue;
            auto it = DataTypeMap.find(name);
            if (it != DataTypeMap.end())
            {
                if(DataTypeMap[name] == 0) data[name] = std::stoi(result[column*i + j]);
                else if(DataTypeMap[name] == 1) data[name] = std::stod(result[column*i + j]);
                else if(DataTypeMap[name] == 3)
                {
                    Json::Value temp;
                    Json::Reader reader;
                    if(reader.parse(result[column*i + j], temp)) data[name] = temp;
                }
            }
            else
            {
                data[name] = result[column*i + j]; 
            }
        }
        datas[order] = data;
        order++;    
    }
    sqlite3_free_table(result);

    return true;
}

bool DatabaseUtil::DataTypeConvert(const Json::Value& data, const std::string& dataName, std::ostringstream& sqlString, bool isFuzzy)
{
    //在数据类型表中查找该名称变量的数据类型
    auto it = DataTypeMap.find(dataName);
    try
    {
        if (it != DataTypeMap.end())
        {
            if (DataTypeMap[dataName] == 0) sqlString << data[dataName].asInt();
            else if (DataTypeMap[dataName] == 1) sqlString << data[dataName].asDouble();
            else if (DataTypeMap[dataName] == 3) sqlString << '\'' << data[dataName].toStyledString() << '\'';
            return true;
        }
        else
        {
            if(isFuzzy) sqlString << "\'%" << data[dataName].asString() << "%\'";
            else sqlString << '\'' << data[dataName].asString() << '\'';
        }
    }
    catch(const std::exception& e)
    {
        return false;
    }

    return true;
}

//储存各个数据库变量数据类型的表,0为整形，1为浮点数，2为字符串，3为JSON
std::unordered_map<std::string, int> DatabaseUtil::DataTypeMap = {
    //摄像头配置表
    {"CameraAlgorithmConfigs", 3},
    //算法配置表
    {"AlgorithmFrameParam", 0}, {"AlgorithmAlarmFreq", 1}, {"AlgorithmVedioSpan", 1}, {"AlgorithmTimeThreshold", 0},
    {"AlgorithmConfidenceThreshold", 1}, {"AlgorithmPicThresholdWidth", 0}, {"AlgorithmPicThresholdHeight", 0},
    {"AlgorithmIOU", 1}, {"AlgorithmFrameDiffer", 0}, {"AlgorithmTimeSpan", 0},
    {"AlarmHandleInfo", 3}, {"AlgorithmPeriod", 3},
    //报警信息配置表
    {"AlarmTime", 3}, {"AlarmTimeStamp", 0},
    //角色配置表
    {"RolePermission", 3},
    //报警类型配置表
    {"AlarmTypeLevelInfo", 3},
    //字典配置表
    {"DictionaryContent", 3}
};

void DatabaseUtil::Init()
{
    if (hasInited) return;
        
    //打开或创建数据库
    if(sqlite3_open("./database.db", &db) == SQLITE_OK) std::cout << "启动数据库成功。" << std::endl;
    else
    {
        std::cout << "启动数据库失败，错误信息：" << sqlite3_errmsg(db) << std::endl;
        return;
    }

    //创建表
    CreateTable();

    hasInited = true;
}

void DatabaseUtil::CreateTable()
{
    char* errorMsg;

    //创建摄像头配置表
    std::string cameraTableCreateSql = 
    "CREATE TABLE CameraConfig (\
        GUID TEXT PRIMARY KEY,\
        CameraPlatform TEXT,\
        CameraCode TEXT NOT NULL UNIQUE,\
        CameraName TEXT,\
        CameraIP TEXT,\
        CameraAccount TEXT,\
        CameraPassword TEXT,\
        CameraType TEXT,\
        CameraManufacturer TEXT,\
        CameraAreaName TEXT,\
        CameraStreamUrl TEXT,\
        CameraDisplay TEXT,\
        CameraAlgorithmConfigs TEXT,\
        CameraRemark TEXT\
    )";
    if(sqlite3_exec(db, cameraTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建摄像头配置表成功" << std::endl;
    else std::cout << "未创建摄像头配置表，相关信息：" << errorMsg << std::endl;

    //创建算法配置表
    std::string algorithmTableCreateSql = 
    "CREATE TABLE AlgorithmConfig (\
        GUID TEXT PRIMARY KEY,\
        AlgorithmName TEXT,\
        AlgorithmType TEXT,\
        AlgorithmCode TEXT NOT NULL UNIQUE,\
        AlgorithmFrameParam INTEGER,\
        AlgorithmAlarmFreq REAL,\
        AlgorithmVedioSpan REAL,\
        AlgorithmTimeThreshold INTEGER,\
        AlgorithmConfidenceThreshold REAL,\
        AlgorithmPicThresholdWidth INTEGER,\
        AlgorithmPicThresholdHeight INTEGER,\
        AlgorithmIOU REAL,\
        AlgorithmFrameDiffer REAL,\
        AlgorithmPeriod TEXT,\
        AlgorithmTimeSpan INTEGER,\
        AlgorithmRemark TEXT\
    )";
    if(sqlite3_exec(db, algorithmTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建算法配置表成功" << std::endl;
    else std::cout << "未创建算法配置表，相关信息：" << errorMsg << std::endl;

    //创建报警信息表
    std::string alarmTableCreateSql = 
    "CREATE TABLE AlarmInfo (\
        GUID TEXT PRIMARY KEY,\
        AlarmCode TEXT NOT NULL UNIQUE,\
        AlarmType TEXT,\
        AlarmLevel TEXT,\
        AlarmTime TEXT,\
        AlarmTimeStamp INTEGER,\
        AlarmPosition TEXT,\
        AlarmDeviceCode TEXT,\
        AlarmDeviceName TEXT,\
        AlarmStatus TEXT,\
        AlarmHandleInfo TEXT,\
        AlarmPicture TEXT,\
        AlarmVedio TEXT,\
        AlarmRemark TEXT\
    )";
    if(sqlite3_exec(db, alarmTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建报警信息表成功" << std::endl;
    else std::cout << "未创建报警信息表，相关信息：" << errorMsg << std::endl;

    //创建用户信息表
    std::string UserTableCreateSql = 
    "CREATE TABLE UserInfo (\
        GUID TEXT PRIMARY KEY,\
        UserName TEXT,\
        UserCode TEXT NOT NULL UNIQUE,\
        UserDepartment TEXT,\
        UserJob TEXT,\
        UserPhoneNumber TEXT,\
        UserEmail TEXT,\
        UserID TEXT NOT NULL UNIQUE,\
        UserPassword TEXT NOT NULL,\
        UserStatus TEXT,\
        UserRole TEXT,\
        UserRemark TEXT\
    )";
    if(sqlite3_exec(db, UserTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建用户信息表成功" << std::endl;
    else std::cout << "未创建用户信息表，相关信息：" << errorMsg << std::endl;

    //创建角色配置表
    std::string RoleTableCreateSql = 
    "CREATE TABLE RoleConfig (\
        GUID TEXT PRIMARY KEY,\
        RoleName TEXT,\
        RoleCode TEXT NOT NULL UNIQUE,\
        RoleStatus TEXT,\
        RoleIsDefault TEXT,\
        RoleRemark TEXT,\
        RolePermission TEXT\
    )";
    if(sqlite3_exec(db, RoleTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建角色配置表成功" << std::endl;
    else std::cout << "未创建角色配置表，相关信息：" << errorMsg << std::endl;

    //创建区域配置表
    std::string areaTableCreateSql = 
    "CREATE TABLE AreaConfig (\
        GUID TEXT PRIMARY KEY,\
        AreaName TEXT,\
        AreaCode TEXT NOT NULL UNIQUE,\
        AreaParent TEXT,\
        AreaType TEXT,\
        AreaRemark TEXT\
    )";
    if(sqlite3_exec(db, areaTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建区域配置表成功" << std::endl;
    else std::cout << "未创建区域配置表，相关信息：" << errorMsg << std::endl;

    //创建报警类型配置表
    std::string alarmTypeTableCreateSql = 
    "CREATE TABLE AlarmTypeConfig (\
        GUID TEXT PRIMARY KEY,\
        AlarmTypeCode TEXT NOT NULL UNIQUE,\
        AlarmTypeName TEXT,\
        AlarmTypeLevelInfo TEXT\
    )";
    if(sqlite3_exec(db, alarmTypeTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建报警类型配置表成功" << std::endl;
    else std::cout << "未创建报警类型配置表，相关信息：" << errorMsg << std::endl;

    //创建报警处理配置表
    std::string alarmHandleTableCreateSql = 
    "CREATE TABLE AlarmHandleConfig (\
        GUID TEXT PRIMARY KEY,\
        AlarmHandleStatus TEXT,\
        AlarmHandleText TEXT,\
        AlarmHandleType TEXT,\
        AlarmHandleCode TEXT NOT NULL UNIQUE\
    )";
    if(sqlite3_exec(db, alarmHandleTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建报警处理配置表成功" << std::endl;
    else std::cout << "未创建报警处理配置表，相关信息：" << errorMsg << std::endl;

    //创建报警处理配置表
    std::string alarmTemplateTableCreateSql = 
    "CREATE TABLE AlarmTemplateConfig (\
        GUID TEXT PRIMARY KEY,\
        AlarmTemplateName TEXT,\
        AlarmTemplateSystem TEXT,\
        AlarmTemplateModel TEXT,\
        AlarmTemplateContent TEXT\
    )";
    if(sqlite3_exec(db, alarmTemplateTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建推送模板配置表成功" << std::endl;
    else std::cout << "未创建推送模板配置表，相关信息：" << errorMsg << std::endl;

    //创建报警处理配置表
    std::string menuTableCreateSql = 
    "CREATE TABLE MenuConfig (\
        GUID TEXT PRIMARY KEY,\
        MenuType TEXT,\
        MenuName TEXT,\
        MenuCode TEXT NOT NULL UNIQUE,\
        MenuParentCode TEXT,\
        MenuUrl TEXT,\
        MenuActived TEXT,\
        MenuHiden TEXT\
    )";
    if(sqlite3_exec(db, menuTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建菜单管理配置表成功" << std::endl;
    else std::cout << "未创建菜单管理配置表，相关信息：" << errorMsg << std::endl;

    //创建报警处理配置表
    std::string dictionaryTableCreateSql = 
    "CREATE TABLE DictionaryConfig (\
        GUID TEXT PRIMARY KEY,\
        DictionaryCode TEXT NOT NULL UNIQUE,\
        DictionaryName TEXT,\
        DictionaryNameE TEXT,\
        DictionaryContent TEXT,\
        DictionaryRemark TEXT\
    )";
    if(sqlite3_exec(db, dictionaryTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建字典配置表成功" << std::endl;
    else std::cout << "未创建字典配置表，相关信息：" << errorMsg << std::endl;

    //创建日志信息表
    std::string LogTableCreateSql = 
    "CREATE TABLE LogInfo (\
        GUID TEXT PRIMARY KEY,\
        LogUser TEXT,\
        LogContent TEXT,\
        LogServe TEXT,\
        LogModule TEXT,\
        LogStatus TEXT,\
        LogType TEXT,\
        LogIP TEXT,\
        LogOS TEXT,\
        LogBrowser TEXT,\
        LogTime TEXT,\
        LogRemark TEXT\
    )";
    if(sqlite3_exec(db, LogTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "创建日志信息表成功" << std::endl;
    else std::cout << "未创建日志信息表，相关信息：" << errorMsg << std::endl;

    //添加数据库字段的方法
    // menuTableCreateSql = "ALTER TABLE MenuConfig ADD COLUMN MenuParentCode TEXT";
    // if(sqlite3_exec(db, menuTableCreateSql.c_str(), nullptr, nullptr, &errorMsg) == SQLITE_OK) std::cout << "成功添加字段" << std::endl;
    // else std::cout << "添加字段失败，原因：" << errorMsg << std::endl;
}

DatabaseUtil::DatabaseUtil()
{
    hasInited = false;
}

DatabaseUtil::~DatabaseUtil()
{    
    if (!hasInited) return;
    
    //关闭数据库
    if(sqlite3_close(db) == SQLITE_OK) std::cout << "关闭数据库成功。" << std::endl;
    else std::cout << "关闭数据库失败，错误信息：" << sqlite3_errmsg(db) << std::endl;
}

DatabaseUtil* DatabaseUtil::GetGetInstance()
{
    if(singleObjPtr == nullptr) singleObjPtr = new DatabaseUtil();
    return singleObjPtr;
}

void DatabaseUtil::DeleteInstance()
{
    if(singleObjPtr == nullptr) return;
    delete singleObjPtr;
    singleObjPtr = nullptr;
}
